#!/usr/local/bin/python
# -*- coding: utf-8 -*-


from sqlalchemy import Column, Integer, Float, and_, Text, Date
from sqlalchemy.orm import declarative_base

import init
import toolkit
from init import session


Base = declarative_base()

class CashFlowMonthlyAlchemy(Base):
    '''
    类功能：按照月份统计现金流表的合计数据
    '''
    __tablename__ = "cash_flow_month"

    id = Column(Integer, primary_key=True, autoincrement=True, comment="数据表id值")
    date_time = Column(Date, comment="数字格式的年份和月份时间")
    year = Column(Integer, comment="数据归属年份")
    month = Column(Integer, comment="数据归属月份")
    data_type = Column(Text, comment="标记属于[资产]或[权属]")

    inflow_value = Column(Float, comment="流入金额")
    outflow_value = Column(Float, comment="流出金额")
    difference_value = Column(Float, comment="净流量金额")

    def __init__(self,
                 year=2022,
                 month=1,
                 dataType="",
                 inflowValue=0.0,
                 outflowValue=0.0,
                 differenceValue=0.0
                 ):

        self.date_time = toolkit.setTargetDate(year, month)
        self.year = year
        self.month = month
        self.data_type = dataType



        self.inflow_value  = inflowValue
        self.outflow_value = outflowValue
        self.difference_value = differenceValue

    def AddCashFlowItem(self):
        '''
            函数功能：添加一条数据
            :return:
        '''
        session.add(self)
        session.commit()


    def UpdateCashFlowItem(self):
        '''
        函数功能；更新一条数据,暂未实现
        :return:
        '''

    def SearchCashFlowByMonth(self, year, month):
        '''
        函数功能：以时间和资产负债表科目为条件进行查询
        :param year:
        :param month:
        :return:
        '''

        cashFlowInfoMonthly = session.query(CashFlowMonthlyAlchemy).filter(and_(CashFlowMonthlyAlchemy.year == year,
                                                                        CashFlowMonthlyAlchemy.month == month
                                                                          )).all()

        return cashFlowInfoMonthly

    def SearchCashFlowOwnershipByYear(self, year):
        '''
        函数功能：查询指定年份各月的收支情况
        :param year:
        :return:
        '''
        cashFlowInfo = session.query(CashFlowMonthlyAlchemy)\
                                     .filter(CashFlowMonthlyAlchemy.year==year)  \
                                     .filter(CashFlowMonthlyAlchemy.data_type=="ownership")  \
                                     .all()
        return cashFlowInfo


    def ShowData(self):
        '''
        函数功能：展示读取出来的数据
        :return:
        '''
        pass

    def DelCashFlowByMonth(self, year, month):
        '''
        函数功能，删除指定月份的张博数据
        :param year:
        :param month:
        :return:
        '''

        deleteCashFlowInfo = session.query(CashFlowMonthlyAlchemy).filter(and_(CashFlowMonthlyAlchemy.year==year,
                                                                     CashFlowMonthlyAlchemy.month==month))
        deleteCashFlowInfo.delete()
        session.commit()


    def SearchCashFlowByMonthDataType(self, year, month, dataType):
        '''
        函数功能：以时间和数据类型为条件进行查询
        :param year:
        :param month:
        :return:
        '''

        balanceSheetInfo = session.query(CashFlowMonthlyAlchemy).filter(and_(CashFlowMonthlyAlchemy.year == year,
                                                                          CashFlowMonthlyAlchemy.month == month,
                                                                          CashFlowMonthlyAlchemy.data_type == dataType,
                                                                          )).all()

        return balanceSheetInfo

def main():
    cashFlowMonthlyAlchemy = CashFlowMonthlyAlchemy()
    cashFlowMonthlyAlchemy.AddCashFlowItem()


    '''
    balanceSheetMonthlyAlchemy = BalanceSheetMonthlyAlchemy(year=2022,
                                          month=1,
                                          dataType="property",
                                          startValue=110.11,
                                          endValue=120.22
                                          )

    balanceSheetMonthlyAlchemy.AddBalanceSheetItem()
    '''
#创建数据库表
CashFlowMonthlyAlchemy.metadata.create_all(init.engine)
if __name__ == '__main__':
    main()